#include "sql.h"

#define LOCAL_SQL_SIZE 2048

Status dataOpen(const char* name, sqlite3** db) {
	if (sqlite3_open(name, db) != SQLITE_OK) {
		PRINT_ERR(sqlite3_errmsg(*db));
		return Failure;
	}
	return Success;
}

Status dataClose(sqlite3* db) {
	if (sqlite3_close(db) != SQLITE_OK) {
		PRINT_ERR(sqlite3_errmsg(db));
		return Failure;
	}
	return Success;
}

Status dataCreatTable(sqlite3* db, const char* name, const char* item) {
	char* errmsg;
	char sql[LOCAL_SQL_SIZE];

	sprintf(sql, "create table if not exists %s(%s)", name, item);
	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		PRINT_ERR(errmsg);
		sqlite3_free(errmsg);
		return Failure;
	}
	sqlite3_free(errmsg);
	return Success;
}

Status dataInsert(sqlite3* db, const LoginMsg* log, const StaffMsg* stf, const HistoryMsg* his) {
	char* errmsg;
	char sql[LOCAL_SQL_SIZE];

	/* regist */
	if (log && stf) 
	{
		sprintf(sql, "insert into account values('%s','%s','%d','%d');", log->account, log->passwd, log->ID.a_type, log->ID.u_id);
		if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
			sqlite3_free(errmsg);
			return Failure; 	// Account name is already occupied.
		}
		sqlite3_free(errmsg);
		sprintf(sql, "insert into staff values('%d','%s','%d','%d');", stf->ID.u_id, stf->name, stf->age, stf->tel);
		/* A u_id can be bound to multiple accounts */
		if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
		}
		sqlite3_free(errmsg);
	}
	else if (stf)
	{
		/* add staff */
		sprintf(sql, "insert into staff values('%d','%s','%d','%d');", stf->ID.u_id, stf->name, stf->age, stf->tel);
	    /* Staff INFO can be inserted repeatedly */
		if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
			return Failure;
		}
		sqlite3_free(errmsg);
	}
	else if (his)
	{
		sprintf(sql, "insert into history values('%d', '%s', '%s');", his->ID.a_id, his->action, his->my_time);
		/* This error can be ignored */
		if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
		}
		sqlite3_free(errmsg);
	}
	return Success;
}

Status dataSearchInfo(sqlite3* db, LoginMsg* log, const StaffMsg* stf, const HistoryMsg* his, char*** result, int* row, int* col) {
	char* errmsg;
	int has_former; 	// using for sql cat
	char sql[LOCAL_SQL_SIZE];
	char temp[LOCAL_SQL_SIZE];
	
	/* login */
	if (log) {
		sprintf(sql, "select rowid,* from account where AccountName = '%s' and Passwd = '%s';", log->account, log->passwd);
		if (sqlite3_get_table(db, sql, result, row, col, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
			sqlite3_free_table(*result);
			sqlite3_free(errmsg);
			return Failure;
		}
		if (*row != 1) {
			sqlite3_free(errmsg);
			sqlite3_free_table(*result);
			return Failure;
		}
		/* fill in ID */
		log->ID.a_id = atoi((*result)[(*row) * (*col)]);
		log->ID.a_type = (enum AccountType)atoi((*result)[(*row) * (*col) + 3]);
		log->ID.u_id = atoi((*result)[(*row) * (*col) + 4]);
		sqlite3_free_table(*result);
		sqlite3_free(errmsg);
	}
	if (stf) {
		has_former = 0;
		if (stf->flag < 0) {
			sprintf(sql, "select * from staff;");
		} else if (stf->flag & S_uID) {
			sprintf(sql, "select * from staff where EmployeeID = '%d';", stf->ID.u_id);
		} else {
			sprintf(sql, "select * from staff where ");
			if (stf->flag & S_Name) {
				sprintf(temp, "Name = '%s' ", stf->name);
				strcat(sql, temp);
				has_former = 1;
			}
			if (stf->flag & S_Age) {
				if (has_former) {
					sprintf(temp, "and Age = '%d' ", stf->age);
				} else {
					sprintf(temp, "Age = '%d' ", stf->age);
					has_former = 1;
				}
				strcat(sql, temp);
			}
			if (stf->flag & S_Tel) {
				if (has_former) {
					sprintf(temp, "and Tel = '%d' ", stf->tel);
				} else {
					sprintf(temp, "Tel = '%d' ", stf->tel);
					has_former = 1;
				}
				strcat(sql, temp);
			}
			sql[strlen(sql)-1] = ';'; 	// replace ' ' by ';'
		}
		if (sqlite3_get_table(db, sql, result, row, col, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
			sqlite3_free_table(*result);
			sqlite3_free(errmsg);
			return Failure;
		}
		if (*row == 0) {
			sqlite3_free(errmsg);
			sqlite3_free_table(*result);
			return Failure;
		}
		/* get_free_table after send msgs */
		sqlite3_free(errmsg);
	}
	if (his) {
		if (his->ID.a_type == Admin) {
			sprintf(sql, "select * from history;");
		} else {
			sprintf(sql, "select * from history where AccountID = '%d';", his->ID.a_id);
		}
		if (sqlite3_get_table(db, sql, result, row, col, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
			sqlite3_free_table(*result);
			sqlite3_free(errmsg);
			return Failure;
		}
		if (*row == 0) {
			sqlite3_free(errmsg);
			sqlite3_free_table(*result);
			return Failure;
		}
		/* get_free_table after send msgs */
		sqlite3_free(errmsg);
	}
	return Success;
}

Status dataDelete(sqlite3* db, const LoginMsg* log, const StaffMsg* stf, const HistoryMsg* his) {
	char* errmsg;
	char sql[LOCAL_SQL_SIZE];
	/* staff */
	if (stf) {
		/* delete staff */
		if (stf->flag & S_uID) {
			sprintf(sql, "delete from staff where EmployeeID = '%d';", stf->ID.u_id);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
		if (stf->flag & S_Name) {
			sprintf(sql, "delete from staff where Name = '%s';", stf->name);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
		if (stf->flag & S_Age) {
			sprintf(sql, "delete from staff where Age = '%d';", stf->age);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
		if (stf->flag & S_Tel) {
			sprintf(sql, "delete from staff where Tel = '%d';", stf->tel);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
		/* delete account if exists */
		sprintf(sql, "delete from account where EmployeeID = '%d';", stf->ID.u_id);
		sqlite3_exec(db, sql, NULL, NULL, NULL);
	}
	if (his) {
		if (his->ID.a_type == Admin) {
			sprintf(sql, "delete from history;");
		} else {
			sprintf(sql, "delete from history where AccountID = '%d';", his->ID.a_id);
		}
		if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			PRINT_ERR(errmsg);
			sqlite3_free(errmsg);
			return Failure;
		}
		sqlite3_free(errmsg);
	}
	return Success;
}

Status dataUpdateInfo(sqlite3* db, const LoginMsg* log, const StaffMsg* stf, const HistoryMsg* his) {
	char* errmsg;
	char sql[LOCAL_SQL_SIZE];
	/* staff */	
	if (stf) {
		/* update staff */
		if (stf->flag & S_Name) {
			sprintf(sql, "update staff set Name = '%s' where EmployeeID = '%d';", stf->name, stf->ID.u_id);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
		if (stf->flag & S_Age) {
			sprintf(sql, "update staff set Age = '%d' where EmployeeID = '%d';", stf->age, stf->ID.u_id);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
		if (stf->flag & S_Tel) {
			sprintf(sql, "update staff set tel = '%d' where EmployeeID = '%d';", stf->tel, stf->ID.u_id);
			if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				PRINT_ERR(errmsg);
				sqlite3_free(errmsg);
				return Failure;
			}
			sqlite3_free(errmsg);
		}
	}
	return Success;
}

